package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

/**
 * 盲样与抽测
 * @author wzj
 *
 */
@Service
public class BlindSampleService {
	
	
	/**
	 * 获取盲样与抽测
	 * @param params
	 * @return
	 * @throws Exception 
	 */
	public List<RowBase> queryBlindSample(Map<String, String> params,ITable pTable, PageInfo pageInfo) throws Exception{
	
		String ThanSql="select t1.*, t6.monitorparamname,t8.c_userealname,t3.companyname, t7.pmad from qc_water_than t1"
					  +" left join qc_companyinfo t3 on t3.COMPANYCODE = t1.companyid"
					  +" left join qc_monitorparam t6 on t6.monitorparamcode = t1.parmname and t6.monitortypecode = t1.monitortype"
					  +" left join qc_monitorparamdetail t7 on t7.paramguid = t6.guid "
					  +" and t7.mcdownlimit < t1.value and t7.mcuplimit > t1.value "
					  + " left join safe_user t8 on t8.s_userid=t1.userid where 1 = 1";	          
	     if(StringUtil.IsNotNullOrEmpty(params.get("start"))){	   		
	   		ThanSql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";	   	
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("end"))){
	   		ThanSql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("stationid"))){   		
	   		ThanSql+="  and t1.stationid='"+params.get("stationid")+"'";
	   	}
		if(StringUtil.IsNotNullOrEmpty(params.get("thanType"))){   		
	   		ThanSql+="  and t1.thantype in("+params.get("thanType")+")";
	   	} 
	   	ThanSql+=" and t6.isfieldparam='1' order by t3.companyname, t1.datetime desc";  
		List<GlobalParameter> listParam = new ArrayList<>();		
		return DataUtil.getPageRow(ThanSql, listParam, pTable, pageInfo);

	}

	/**
	 * 获取盲样与抽测
	 * @param params
	 * @return
	 * @throws Exception 
	 */
	public List<RowBase> outBlindSample(Map<String, String> params,ITable pTable, PageInfo pageInfo) throws Exception{
	
		String ThanSql="select t1.*, t6.monitorparamname,t8.c_userealname,t3.companyname, t7.pmad from qc_water_than t1"
					  +" left join qc_companyinfo t3 on t3.COMPANYCODE = t1.companyid"
					  +" left join qc_monitorparam t6 on t6.monitorparamcode = t1.parmname and t6.monitortypecode = t1.monitortype"
					  +" and t6.isfieldparam='1' left join qc_monitorparamdetail t7 on t7.paramguid = t6.guid "
					  +" and t7.mcdownlimit < t1.value and t7.mcuplimit > t1.value "
					  + " left join safe_user t8 on t8.s_userid=t1.userid where 1 = 1";	          
	     if(StringUtil.IsNotNullOrEmpty(params.get("start"))){	   		
	   		ThanSql+="  and t1.datetime>=to_date('"+params.get("start")+" 00:00:00','yyyy-mm-dd hh24:mi:ss')";	   	
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("end"))){
	   		ThanSql+="  and t1.datetime<=to_date('"+params.get("end")+" 23:59:59','yyyy-mm-dd hh24:mi:ss')";
	   	}
	   	if(StringUtil.IsNotNullOrEmpty(params.get("stationid"))){   		
	   		ThanSql+="  and t1.stationid='"+params.get("stationid")+"'";
	   	}
		if(StringUtil.IsNotNullOrEmpty(params.get("thanType"))){   		
	   		ThanSql+="  and t1.thantype in("+params.get("thanType")+")";
	   	} 
	   	ThanSql+=" order by t3.companyname, t1.datetime desc";  
		List<GlobalParameter> listParam = new ArrayList<>();		
		return DataUtil.getSelectRows(ThanSql, listParam);

	}
	/**
	 * 根据公司编号，获取参数信息
	 * @param params
	 * @return
	 * @throws Exception 
	 */
	public List<RowBase> getCompanyParam(Map<String, String> params) throws Exception{
	
		String sql="select t2.monitorparamcode,t2.monitorparamname from  qc_companyparaminfo t1"
					  +" left join qc_monitorparam t2 on t2.guid=t1.paramguid where  t2.isfieldparam='1'";	          
		if(StringUtil.IsNotNullOrEmpty(params.get("COMPANYID"))){ 
			sql+="  and t1.companycode in("+ParamPartition(params.get("COMPANYID"))+") ";
	   	}
		if(StringUtil.IsNotNullOrEmpty(params.get("moniitorType"))){ 
			sql+="  and t2.monitortypecode in("+ParamPartition(params.get("moniitorType"))+") ";
	   	}
		    sql+=" group by t2.monitorparamcode,t2.monitorparamname";
		if(StringUtil.IsNotNullOrEmpty(params.get("COMPANYID"))){ 
			if(params.get("COMPANYID").split(",").length!=2){
				sql+=" having count(*)>="+(params.get("COMPANYID").split(",").length-1)+" "; 
			}		
		}else{
			sql+=" having count(*)>="+params.get("COMPANYCOUNT")+" "; 
		}
			 		
		return DataUtil.getSelectRows(sql,null);

	}
	/**
	 * 根据公司获取监测类型
	 * @param params
	 * @return
	 */
	public List<RowBase> getMonitorType(Map<String, String> params){
		String sql="select t1.s_domaincaption,t.monitoringtypecode from qc_companydetailinfo t  left join data_defulatdomain t1" 
				  +" on t1.s_domaincode=t.monitoringtypecode and t1.s_domainname='质控监测类型' where 1=1";
		if(StringUtil.IsNotNullOrEmpty(params.get("COMPANYID"))){
			sql+="  and t.companycode in("+ParamPartition(params.get("COMPANYID"))+")";
		}
		sql+=" group by t1.s_domaincaption,t.monitoringtypecode";
		if(StringUtil.IsNotNullOrEmpty(params.get("COMPANYID"))){ 
			if(params.get("COMPANYID").split(",").length!=2){
				sql+=" having count(t.monitoringtypecode)>="+(params.get("COMPANYID").split(",").length-1)+" "; 
			}		
		}else{
			sql+=" having count(t.monitoringtypecode)>="+params.get("COMPANYCOUNT")+" "; 
		}
		return DataUtil.getSelectRows(sql,null);
	}

	public String ParamPartition(String paramData){
		String[] param=paramData.split(",");
		String StrParam="";
		for(int i=0;i<param.length;i++){
			if(param.length-i==1){
				StrParam+="'"+param[i]+"'";
			}else{
				StrParam+="'"+param[i]+"',";
			}			
		}
		return StrParam;
	}
}
